﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CSMSys.Web.Reports;


namespace CSMSys.Web.Reports
{
    public partial class TestReport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String code = Request.QueryString["code"];
            String name = Request.QueryString["name"];
            String fName = Request.QueryString["fName"];
            String village = Request.QueryString["village"];

            ReportDocument rptDoc = new ReportDocument();
            dsAgreementReport ds = new dsAgreementReport();
            DataTable dt = new DataTable();
            dt.TableName = "Crystal Report Example";
            // dt = getAllOrders(); //This function is located below this function
            dt = getFakeDbOrders();

            ds.Tables[0].Merge(dt);
            rptDoc.Load(Server.MapPath("../Reports/rptAgreementReport.rpt"));
            rptDoc.FileName = Server.MapPath("../Reports/rptAgreementReport.rpt");
            rptDoc.SetDataSource(ds);
            //report.SetParameterValue("@Userid", userid);
            rptDoc.SetParameterValue("CustomerCode", code);
            rptDoc.SetParameterValue("CustomerName", name);
            rptDoc.SetParameterValue("FatherName", fName);
            rptDoc.SetParameterValue("village", village);
            //CrystalReportViewer1.ReportSource = rptDoc;
        }

        public DataTable getFakeDbOrders()
        {
            DataTable dt = CreateNewDT();

            DataRow NewRow = dt.NewRow();
            NewRow["Customer_Name"] = "Scott Smith";
            NewRow["Order_ID"] = 12345;
            NewRow["Product_Name"] = "Some Product";
            NewRow["Product_Qty"] = 5;
            dt.Rows.Add(NewRow);

            DataRow NewRow2 = dt.NewRow();
            NewRow2["Customer_Name"] = "Lynette Smith";
            NewRow2["Order_ID"] = 12365;
            NewRow2["Product_Name"] = "Some Other Product";
            NewRow2["Product_Qty"] = 2;
            dt.Rows.Add(NewRow2);

            return dt;
        }

        public DataTable CreateNewDT()
        {
            DataTable dt = new DataTable();
            DataColumn Customer_Name = new DataColumn("Customer_Name", typeof(string));
            DataColumn Order_ID = new DataColumn("Order_ID", typeof(int));
            DataColumn Product_Name = new DataColumn("Product_Name", typeof(string));
            DataColumn Product_Qty = new DataColumn("Product_Qty", typeof(int));
            dt.Columns.Add(Customer_Name);
            dt.Columns.Add(Order_ID);
            dt.Columns.Add(Product_Name);
            dt.Columns.Add(Product_Qty);
            return dt;
        }

        public DataTable getAllOrders()
        {
            //Connection string replace 'databaseservername' with your db server name
            string sqlCon = "User ID=sa;PWD=123; server=VSBLAP1;INITIAL CATALOG=CSMSys;" +
                                "PERSISTSECURITY INFO=FALSE;Connect Timeout=0";
            SqlConnection Con = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand();
            DataSet ds = null;
            SqlDataAdapter adapter;
            try
            {
                Con.Open();
                //Stored procedure calling. It is already in sample db.
                cmd.CommandText = "getAllOrders";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = Con;
                ds = new DataSet();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Users");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                if (Con.State != ConnectionState.Closed)
                    Con.Close();
            }
            return ds.Tables[0];
        }
    }
}